package com.scalar.chiptrack.operations.bins.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.BinSetup;
import com.scalar.chiptrack.operations.bins.forms.AssemblyBinForm;
import com.scalar.chiptrack.utils.SqlQueryMappings;

import java.util.ArrayList;
import java.sql.*;

import org.apache.struts.util.LabelValueBean;

public class AssemblyBinDAO
{
    public AssemblyBinDAO() {}

    public static ArrayList loadAllAssemblyProductsByBaseProductId( int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            if( productId == -1 )
            {
                pstmt = conn.prepareStatement ("Select P.ProcessStep_ID, P.Part_Number FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'ASSEMBLY' ORDER BY Part_Number");
            }
            else
            {
                pstmt = conn.prepareStatement ("Select P.ProcessStep_ID, P.Part_Number FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'ASSEMBLY' AND P_Parent_Product_ID = ? ORDER BY Part_Number");
            }
            pstmt.setInt( 1, productId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                productList.add( new LabelValueBean( rs.getString("Part_Number") , rs.getString( "ProcessStep_ID" ) ) );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Assembly Part Number Info from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return productList;
    }

    public static ArrayList loadBinInformation( int assemblyProcessStepID ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList binInformationList = new ArrayList();
        ArrayList downsizedBinList = new ArrayList();
        BinSetup binSetup = null;
        BinSetup downsizeBinSetup = null;
        int isMapExist = 0;
        //int sortProcessStepId = -1;

        try
        {
            conn = DataSource.getConnection();

            //pstmt = conn.prepareStatement ("SELECT WSBT_Bin_Number, WSBT_Bin_Type, ( SELECT COUNT(*) FROM Assembly_Bin_Mapping ABM WHERE ABM.Bin_ProcessStep_ID = WSBT.ProcessStep_ID AND ABM.Bin_Number =  WSBT.WSBT_Bin_Number ) AS Is_Map_Exist  FROM Wip_Step_Bin_Table WSBT WHERE ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM fn_get_process_flow_backward_by_id ( ? ) WHERE Process_Type_Name = 'SORT' ) ");
            pstmt = conn.prepareStatement ("SELECT WIP_BIN_TABLE.WSBT_Bin_Number, ABM.Bin_Number AS ABM_Bin_Number, ABM.Downsize_Bin_Number FROM ( SELECT ProcessStep_ID, WSBT_Bin_Number FROM Wip_Step_Bin_Table WSBT WHERE ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM fn_get_process_flow_backward_by_id ( ? ) WHERE Process_Type_Name = 'SORT' ) AND WSBT_Bin_Type = 'Accept' ) AS WIP_BIN_TABLE LEFT OUTER JOIN Assembly_Bin_Mapping ABM ON WIP_BIN_TABLE.ProcessStep_ID = ABM.Bin_ProcessStep_ID AND WIP_BIN_TABLE.WSBT_Bin_Number = ABM.Bin_Number AND ABM.Assm_ProcessStep_ID = ? ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) ");
            pstmt.setInt( 1, assemblyProcessStepID );
            pstmt.setInt( 2, assemblyProcessStepID );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                binSetup = new BinSetup();
                binSetup.setBinNo( rs.getString( "WSBT_Bin_Number" ) ) ;
                binSetup.setBinType( "Good Bin" );
                binSetup.setSelectedBin( ( rs.getString( "ABM_Bin_Number" ) == null ? false : true ) );
                binSetup.setDownsizedBinNo( rs.getString( "Downsize_Bin_Number" ) ) ;
                binInformationList.add( binSetup );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Assembly Part Number Info from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        //THIS IS COMMENTED BECAUSE DOWNSIZE LIST BOX IS REMOVED FROM JSP SCREEN.
        /*for( int i = 0; i < binInformationList.size(); i++  )
        {
            downsizeBinSetup = ( BinSetup ) binInformationList.get( i );
            downsizeBinSetup.setDownsizedBinList( loadDownsizeBins( assemblyProcessStepID, downsizeBinSetup.getBinNo() ) );
        }*/

        return binInformationList;
    }

    public static void saveBinDetails( DBTransaction dbTransaction, AssemblyBinForm assemblyBinForm ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ArrayList binDetailList = null;
        int binDetailListSize   = 0;
        BinSetup binSetup       = null;

        try
        {
            if(dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (" { CALL SP_SAVE_ASSEMBLY_BIN_MAP_DETAILS( ?, ?, ?, ? ) } ");
            binDetailList = assemblyBinForm.getBinInformationList();
            binDetailListSize = binDetailList.size();
            for( int i = 0; i < binDetailListSize; i++  )
            {
                binSetup = ( BinSetup ) binDetailList.get( i );
                cstmt.setInt    ( 1, assemblyBinForm.getAssemblyProccessStepId() );
                cstmt.setString ( 2, binSetup.getBinNo() );
                cstmt.setInt    ( 3, ( binSetup.isSelectedBin()?  0 : 1 ) );
                cstmt.setString ( 4, null );
                //THIS IS COMMENTED BECAUSE DOWNSIZE LIST BOX IS REMOVED FROM JSP SCREEN.
                /*if( binSetup.getDownsizedBinNo() != "0" )
                {
                    cstmt.setString ( 4, binSetup.getDownsizedBinNo() );
                }
                else
                {
                    cstmt.setString ( 4, null );
                }*/
                //cstmt.addBatch();
                cstmt.executeUpdate();
            }
            //cstmt.executeBatch();

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save Bin Details ", sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static ArrayList loadAllChipsBaseProducts() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList baseProductslist = new ArrayList();
        String productId = null;

        try
        {
            conn = DataSource.getConnection();

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.process.product.getAllBaseProductsList") );
            rs = ps.executeQuery();
            while( rs.next() )
            {
                productId = rs.getString("Product_ID");
                if( rs.getString( "P_Type" ) == null && productId != null )
                {
                    baseProductslist.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("IST_Manufacturing_Part_Number"), productId ) );
                }
            }
        }
        catch ( SQLException se )
        {
            se.printStackTrace();
            throw new ScalarException ("Could not load all chip base products information " , se);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }
            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return baseProductslist;
    }

    public static ArrayList loadDownsizeBins(int assemblyProcessStepID, String BinNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList downsizeBinslist = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            ps = conn.prepareStatement ( "SELECT WSBT_Bin_Number FROM Wip_Step_Bin_Table WHERE ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM fn_get_process_flow_backward_by_id ( ? ) WHERE Process_Type_Name = 'SORT' ) AND WSBT_Bin_Type = 'Accept' AND CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) ) AS INT ) > CAST( SUBSTRING( ? , 4, LEN( ? ) ) AS INT ) ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) " );
            ps.setInt    ( 1, assemblyProcessStepID  );
            ps.setString ( 2, BinNumber);
            ps.setString ( 3, BinNumber);

            rs = ps.executeQuery();
            while( rs.next() )
            {
                downsizeBinslist.add ( rs.getString("WSBT_Bin_Number") );
            }
        }
        catch ( SQLException se )
        {
            se.printStackTrace();
            throw new ScalarException ("Could not load all chip base products information " , se);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }
            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return downsizeBinslist;
    }

    public static ArrayList loadBaseProductBinInformation( int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList binInformationList = new ArrayList();
        ArrayList downsizedBinList = new ArrayList();
        BinSetup binSetup = null;
        BinSetup downsizeBinSetup = null;
        int isMapExist = 0;
        //int sortProcessStepId = -1;

        try
        {
            conn = DataSource.getConnection();

            //pstmt = conn.prepareStatement ("SELECT WIP_BIN_TABLE.WSBT_Bin_Number, ABM.Bin_Number AS ABM_Bin_Number, ABM.Downsize_Bin_Number FROM ( SELECT ProcessStep_ID, WSBT_Bin_Number FROM Wip_Step_Bin_Table WSBT WHERE ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM fn_get_process_flow_backward_by_id ( ? ) WHERE Process_Type_Name = 'SORT' ) AND WSBT_Bin_Type = 'Accept' ) AS WIP_BIN_TABLE LEFT OUTER JOIN Assembly_Bin_Mapping ABM ON WIP_BIN_TABLE.ProcessStep_ID = ABM.Bin_ProcessStep_ID AND WIP_BIN_TABLE.WSBT_Bin_Number = ABM.Bin_Number AND ABM.Assm_ProcessStep_ID = ? ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) ");
            //pstmt = conn.prepareStatement ("SELECT ProcessStep_ID, WSBT_Bin_Number, '' AS ABM_Bin_Number, '' AS Downsize_Bin_Number  FROM Wip_Step_Bin_Table WSBT WHERE WSBT.ProcessStep_ID = (SELECT PS.ProcessStep_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = 'SORT' AND P.P_PARENT_PRODUCT_ID = ? ) AND WSBT_Bin_Type = 'Accept' ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) ");
            pstmt = conn.prepareStatement ("SELECT WIP_BIN_TABLE.WSBT_Bin_Number, MBM.Bin_Number AS MBM_Bin_Number, MBM.Downsize_Bin_Number FROM ( SELECT ProcessStep_ID, WSBT_Bin_Number FROM Wip_Step_Bin_Table WSBT WHERE ProcessStep_ID = (SELECT  top 1 PS.ProcessStep_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = 'SORT' AND P.P_PARENT_PRODUCT_ID = ? ) AND WSBT_Bin_Type = 'Accept' ) AS WIP_BIN_TABLE LEFT OUTER JOIN MRP_Binning_Mapping MBM ON WIP_BIN_TABLE.WSBT_Bin_Number = MBM.Bin_Number AND MBM.Base_Product_ID = ? ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) ");
            pstmt.setInt( 1, productId );
            pstmt.setInt( 2, productId );
            rs = pstmt.executeQuery();
            while( rs.next() )
            {
                binSetup = new BinSetup();
                binSetup.setBinNo( rs.getString( "WSBT_Bin_Number" ) ) ;
                binSetup.setBinType( "Good Bin" );
                binSetup.setSelectedBin( ( rs.getString( "MBM_Bin_Number" ) == null ? false : true ) );
                binSetup.setDownsizedBinNo( rs.getString( "Downsize_Bin_Number" ) ) ;
                binInformationList.add( binSetup );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load MRB Bin Info from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        for( int i = 0; i < binInformationList.size(); i++  )
        {
            downsizeBinSetup = ( BinSetup ) binInformationList.get( i );
            downsizeBinSetup.setDownsizedBinList( loadBaseProductDownsizeBins( productId, downsizeBinSetup.getBinNo() ) );
        }

        return binInformationList;
    }

    public static ArrayList loadBaseProductDownsizeBins(int baseProductId, String BinNumber ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList downsizeBinslist = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            ps = conn.prepareStatement ( "SELECT WSBT_Bin_Number FROM Wip_Step_Bin_Table WHERE ProcessStep_ID = ( SELECT top 1 PS.ProcessStep_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE_NAME = 'SORT' AND P.P_PARENT_PRODUCT_ID = ? ) AND WSBT_Bin_Type = 'Accept' AND CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) ) AS INT ) > CAST( SUBSTRING( ? , 4, LEN( ? ) ) AS INT ) ORDER BY CAST( SUBSTRING( WSBT_Bin_Number , 4, LEN( WSBT_Bin_Number ) )  AS INT ) " );
            ps.setInt    ( 1, baseProductId  );
            ps.setString ( 2, BinNumber);
            ps.setString ( 3, BinNumber);

            rs = ps.executeQuery();
            while( rs.next() )
            {
                downsizeBinslist.add ( rs.getString("WSBT_Bin_Number") );
            }
        }
        catch ( SQLException se )
        {
            se.printStackTrace();
            throw new ScalarException ("Could not load all chip base products information " , se);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }
            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return downsizeBinslist;
    }

    public static void saveBaseProductBinDetails( DBTransaction dbTransaction, AssemblyBinForm assemblyBinForm ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ArrayList binDetailList = null;
        int binDetailListSize   = 0;
        BinSetup binSetup       = null;

        try
        {
            if(dbTransaction == null)
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall (" { CALL SP_SAVE_BASE_PRODUCT_BIN_MAP_DETAILS( ?, ?, ?, ? ) } ");
            binDetailList = assemblyBinForm.getBinInformationList();
            binDetailListSize = binDetailList.size();
            for( int i = 0; i < binDetailListSize; i++  )
            {
                binSetup = ( BinSetup ) binDetailList.get( i );
                cstmt.setInt    ( 1, assemblyBinForm.getBaseProductId() );
                cstmt.setString ( 2, binSetup.getBinNo() );
                cstmt.setInt    ( 3, ( binSetup.isSelectedBin()?  0 : 1 ) );
                if( binSetup.getDownsizedBinNo() != "0" )
                {
                    cstmt.setString ( 4, binSetup.getDownsizedBinNo() );
                }
                else
                {
                    cstmt.setString ( 4, null );
                }
                //cstmt.addBatch();
                //cstmt.executeQuery();
                cstmt.executeUpdate();
            }
            //cstmt.executeBatch();

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not save MRB Bin Details ", sqle);
        }

        finally {

            if (dbTransaction == null) {
            	try {
                    if (conn != null) {
                        conn.commit();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

}
